package com.huatai.bi.service.impl;

import cn.hutool.core.text.csv.*;
import cn.hutool.core.util.CharsetUtil;
import cn.hutool.extra.pinyin.PinyinUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.huatai.bi.service.ImportExcelService;
import com.huatai.bi.utils.StringBuildUtil;
import com.huatai.bi.vo.excelVo.ExcelDataVO;
import com.huatai.bi.vo.excelVo.HeardVO;
import com.huatai.bi.vo.excelVo.SubClassVO;
import com.huatai.common.error.exception.ServiceException;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * @BelongsProject: itc-airport-cloud
 * @BelongsPackage: com.itc.bi.service.impl
 * @Author: hecaiy
 * @CreateTime: 2024-06-13  10:16
 * @Description: TODO  文件操作接口实现层
 * @Version: 1.0
 */
@Service
public class ImportExcelServiceImpl implements ImportExcelService {
    @Autowired
    private ImportExcelEasyPoiService easyPoiService;
//    @Autowired
//    private DirectoryService directoryService;
//    @Autowired
//    private DirectDataSetService directDataSetService;
//    @Autowired
//    private SyncImportDataBaseImpl syncImportDataBase;
//    @Autowired
//    private DataSetColumnMapper dataSetColumnMapper;

    /**
     * 修改excel数据集数据
     * @param file  文件
     * @param sheet 文件对应的sheet
     * @return
     */
    public Boolean updateFileDataSet(MultipartFile file,String sheetName,Long dtId) throws IOException {
        return easyPoiService.updateFileDataSet(file,sheetName,dtId);
//        if(ObjectUtils.isEmpty(dtId)){
//            throw new ServiceException("数据集不能为空");
//        }
//        DirectoryEntity directoryEntity = directoryService.getById(dtId);
//        if(ObjectUtil.isEmpty(directoryEntity)){
//            throw new ServiceException("数据集不存在");
//        }
//        String originalFilename = file.getOriginalFilename();
//        List<SubClassVO> subClassVOS = new ArrayList<>();
//        //判断文件数据是否是csv文件
//        if (originalFilename.endsWith(".csv")) {
//            subClassVOS  = csvImport(file, -1, sheetName,true);
//        }else {
//            subClassVOS = excelImport(file, -1, sheetName,true);
//        }
//        if (CollectionUtil.isNotEmpty(subClassVOS)){
//            List<DataSetColumnEntity> dataSetColumnEntities = dataSetColumnMapper.selectList(Wrappers.<DataSetColumnEntity>lambdaQuery().eq(DataSetColumnEntity::getDataSetId, dtId));
//            //判断数据项是否完全相同
//            SubClassVO subClassVO = subClassVOS.get(0);
//            List<HeardVO> heardVos = subClassVO.getHeardVos();
//            if(heardVos.size()!=dataSetColumnEntities.size()){
//                throw new ServiceException("数据项不一致，数据导入失败");
//            }
//            for (int i = 0; i < heardVos.size(); i++) {
//                HeardVO heardVO = heardVos.get(i);
//                DataSetColumnEntity setColumnEntity = dataSetColumnEntities.stream().filter(p -> p.getColumnName().equalsIgnoreCase(heardVO.getKey())).findFirst().orElse(null);
//                //判断字段是否一致
//                if(ObjectUtil.isEmpty(setColumnEntity)){
//                    throw new ServiceException("数据项不一致，数据导入失败");
//                }
//            }
//            String tableName = directoryEntity.getTableName();
//            tableName = tableName.substring(0, tableName.lastIndexOf("_"));
//            tableName = tableName+"_"+IdWorker.getId();
//            syncImportDataBase.importDataBaseUpdate(subClassVOS.get(0),tableName,directoryEntity.getTableName(),dataSetColumnEntities,directoryEntity);
//            return true;
//        }
//        return false;
    }

    /**
     * 创建excel数据集
     * @param file  文件
     * @param sheet 文件对应的sheet
     * @return
     */
//    @Transactional(rollbackFor = Exception.class)
    public Boolean createFileDataSet(MultipartFile file,List<String> sheet,Long dtId) throws IOException {
        return easyPoiService.createFileDataSet(file,sheet,dtId);
//        if(ObjectUtils.isEmpty(dtId)){
//            throw new ServiceException("目录不能为空");
//        }
//        String originalFilename = file.getOriginalFilename();
//        List<SubClassVO> subClassVOS = new ArrayList<>();
//        List<DirectoryEntity> directoryEntityList = new ArrayList<>();
//        List<DirectDataSetEntity> dataSetEntityList = new ArrayList<>();
//
//        for (String sheetName : sheet){
//            //判断文件数据是否是csv文件
//            if (originalFilename.endsWith(".csv")) {
//                subClassVOS  = csvImport(file, -1, sheetName,true);
//            }else {
//                subClassVOS = excelImport(file, -1, sheetName,true);
//            }
//            if (CollectionUtil.isNotEmpty(subClassVOS)){
//                //创建数据集
//                if(CollectionUtil.isNotEmpty(subClassVOS.get(0).getBodyVos())&&CollectionUtil.isNotEmpty(subClassVOS.get(0).getHeardVos())){
//                    String tablenameNote = originalFilename.substring(0, originalFilename.lastIndexOf("."))+subClassVOS.get(0).getSheetName();
//                    //数据表名称用stic_拼音首字母+时间戳
//                    String tableName = "stic_"+PinyinUtil.getFirstLetter(subClassVOS.get(0).getSheetName(),"")+"_"+ IdWorker.getId();
//                    //TODO 此处还差添加数据集目录逻辑
//                    DirectoryEntity directoryEntity = new DirectoryEntity();
//                    directoryEntity.setName(tablenameNote);
//                    long count = directoryService.count((Wrappers.<DirectoryEntity>lambdaQuery().eq(DirectoryEntity::getPid, dtId).likeRight(DirectoryEntity::getName, tablenameNote)));
//                    if(count>0){
//                        long temp = count+1;
//                        directoryEntity.setName(tablenameNote+temp);
//                    }
//                    directoryEntity.setIsDir( DataDirectorConstant.ISDATASET);
//                    directoryEntity.setPid(dtId);
//                    directoryEntity.setTableName(tableName);
//                    directoryEntity.setDtId(IdWorker.getId());
//                    directoryEntityList.add(directoryEntity);
//                    DirectDataSetEntity directDataSetEntity = new DirectDataSetEntity();
//                    directDataSetEntity.setId(directoryEntity.getDtId());
//                    directDataSetEntity.setDtId(dtId);
//                    directDataSetEntity.setTableName(tableName);
//                    directDataSetEntity.setType(SystemConstant.DATA_SET_TYPE_EXCEL);
//                    dataSetEntityList.add(directDataSetEntity);
//                    syncImportDataBase.importDataBase(subClassVOS.get(0),tableName,directDataSetEntity.getId());
//                }
//            }
//        }
//        if(CollectionUtil.isNotEmpty(directoryEntityList) && CollectionUtil.isNotEmpty(dataSetEntityList)){
//            directDataSetService.saveBatch(dataSetEntityList);
//            directoryService.saveBatch(directoryEntityList);
//            return true;
//        }
//        return false;
    }


    /**
     * 上传文件，获取左侧树结构
     * @param file
     * @return
     * @throws IOException
     */
    @Override
    public ExcelDataVO getExcelImportSheetTree(MultipartFile file) throws IOException {
        return easyPoiService.getExcelImportSheetTree(file);
//        ExcelDataVO excelDataVO = importExcel(file, "");
//        if(!ObjectUtils.isEmpty(excelDataVO)){
//            List<SubClassVO> subClassVOS = excelDataVO.getSubClassVOS();
//            //只返回sheet名称不返回数据
//            subClassVOS.forEach(subClassVO -> {
//                subClassVO.setBodyVos(null);
//                subClassVO.setHeardVos(null);
//            });
//        }
//        return excelDataVO;
    }

    /**
     * 上传文件，根据sheet名称获取数据
     * @param file
     * @return
     * @throws IOException
     */
    public SubClassVO getExcelImportSheetData(MultipartFile file,String sheetName) throws IOException {
        return easyPoiService.getExcelImportSheetData(file,sheetName,100);
//        SubClassVO excelDataVOS = new SubClassVO();
//        ExcelDataVO excelDataVO = importExcel(file, sheetName);
//        if(!ObjectUtils.isEmpty(excelDataVO)){
//            List<SubClassVO> subClassVOS = excelDataVO.getSubClassVOS();
//            if(CollectionUtil.isNotEmpty(subClassVOS)){
//                SubClassVO subClassVO = subClassVOS.get(0);
//                excelDataVOS.setSheetName(sheetName);
//                excelDataVOS.setBodyVos(subClassVO.getBodyVos());
//                excelDataVOS.setHeardVos(subClassVO.getHeardVos());
//            }
//        }
//        return excelDataVOS;
    }

    /**
     * 导入excel
     * @param file
     * @param sheetName
     * @return
     * @throws IOException
     */
    @Override
    public ExcelDataVO importExcel(MultipartFile file,String sheetName) throws IOException {
        ExcelDataVO excelDataVO = new ExcelDataVO();
        String originalFilename = file.getOriginalFilename();
        String rootName = originalFilename.substring(0, originalFilename.lastIndexOf("."));
        //判断文件数据是否是csv文件
        if (originalFilename.endsWith(".csv")) {
            excelDataVO = new ExcelDataVO(rootName,csvImport(file, 100, rootName,false));
        }else {
            excelDataVO = new ExcelDataVO(rootName,excelImport(file, 100, sheetName,false));
        }
        return excelDataVO;
    }

    /**
     *
     * @param file 文件流
     * @param limit 限定条数 如果不限制则传-1
     * @throws IOException
     */
    public List<SubClassVO> excelImport(MultipartFile file,int limit,String sheetNameParam,Boolean isGetMaxHeardValue) throws IOException {
        List<SubClassVO> subClassVOList = new ArrayList<>();
        List<HeardVO> heardVOList = new ArrayList<>();
        List<String> sheetNames = new ArrayList<>();
        //前端没有传指定数据过来
        if(StringUtils.isEmpty(sheetNameParam)){
            InputStream inputStream = file.getInputStream();
            ExcelReader reader = ExcelUtil.getReader(inputStream);
            inputStream.close();
            sheetNames = reader.getSheetNames();
        }
        else {
            sheetNames = Arrays.asList(sheetNameParam);
        }
        if(!ObjectUtils.isEmpty(sheetNames)){
            //需要去掉空格和_特殊字符
            List<String> sheetNameList = new ArrayList<>();
            //存在特殊字符
            List<String> sheetNameListTemp = new ArrayList<>();
            for(String sheetName:sheetNames){
                StringBuilder stringBuilder = StringBuildUtil.originalFilename(sheetName);
                //是否含有特殊字符
                if(stringBuilder.length()>0){
                    sheetNameListTemp.add(stringBuilder.toString());
                }
                sheetNameList.add(sheetName);
            }
            if(sheetNameListTemp!=null && sheetNameListTemp.size()>0){
                throw new ServiceException("sheet文件名称不允许有特殊字符，特殊字符为"+sheetNameListTemp.toString());
            }
            //读取所有sheet数据
            for (String sheetName:sheetNameList){
                List<Map<String, Object>> bodyMapList = new ArrayList<>();
                //创建数据读取器
                InputStream inputStream = file.getInputStream(); // 重新获取输入流
                ExcelReader readerSheet = ExcelUtil.getReader(inputStream, sheetName);
                inputStream.close();
                //限定多少条数据
                if (limit!=-1){
                    bodyMapList = readerSheet.read(0, 1, limit);
                }
                else {
                    bodyMapList = readerSheet.readAll();
                }
                List<Map<String, Object>> maps = buildHeardBody(bodyMapList, heardVOList, isGetMaxHeardValue);
                if(!ObjectUtils.isEmpty(maps)){
                    subClassVOList.add(new SubClassVO(sheetName,heardVOList,maps));
                }
            }
        }
        return subClassVOList;
    }

    /**
     * 构建表头和表体数据
     * @param bodyMapList         表体数据
     * @param heardVOList         表头数据
     * @param isGetMaxHeardValue  表头类型值是否取最大值
     */
    public List<Map<String, Object>> buildHeardBody(List<Map<String, Object>> bodyMapList,List<HeardVO> heardVOList,Boolean isGetMaxHeardValue){
        List<Map<String, Object>> paramBody = new ArrayList<>();
        Boolean isSetHeard = true;
        //存储拼音首字母和字符数据量最大值
        for (Map<String, Object> map : bodyMapList){
            LinkedHashMap<String,Object> hashMap = new LinkedHashMap<String,Object>();
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                Object value = entry.getValue();
                String key = entry.getKey();
                StringBuildUtil.originalFilenameTS(key);
                //TODO 判断列名是否包含特殊字符
                String commonKey = PinyinUtil.getFirstLetter(key, "");
                //TODO  此处还差拼音重复的情况处理
                if(isSetHeard){
                    String characterType = "string";
                    characterType = getColumnNameType(value);
                    heardVOList.add(new HeardVO(key,key,characterType));
                }
                hashMap.put(key,value);
            }
            paramBody.add(hashMap);
            isSetHeard=false;
        }
        return paramBody;
    }


    private String getColumnNameType(Object value) {
        if(value instanceof Long){
            return  "Float64";
        }
        if (value instanceof Double){
            return "Float64";
        }
        if (value instanceof Float){
            return "Float64";
        }
        if (value instanceof Integer){
            return "Float64";
        }
        if (value instanceof Date){
            return "DateTime";
        }
        return "string";
    }

    /**
     * 读取csv中的数据
     * @param multipartFile
     * @param limit  限定条数 如果不限制则传-1
     * @return
     * @throws IOException
     */
    public List<SubClassVO> csvImport(MultipartFile multipartFile,int limit,String sheetName,Boolean isGetMaxHeardValue) throws IOException {
        //2. 进行配置
        CsvReadConfig csvReadConfig=new CsvReadConfig();
        // 是否跳过空白行
        csvReadConfig.setSkipEmptyRows(true);
        // 是否设置首行为标题行
        csvReadConfig.setContainsHeader(true);
        //构建 CsvReader 对象
        CsvReader csvReader = CsvUtil.getReader(csvReadConfig);
        // 这里转了下 可能会产生临时文件，临时文件目录可以设置，也可以立马删除
        File file = multipartFile2File(multipartFile);
        CsvData read = csvReader.read(file, CharsetUtil.CHARSET_GBK);
        List<Map<String,Object>> mapList = new ArrayList<>();
        List<String> header = read.getHeader();
        List<CsvRow> rows = read.getRows();
        for (CsvRow row : rows) {
            Map<String,Object> map = new HashMap<>();
            for (int i = 0; i < row.size(); i++) {
                map.put(header.get(i),row.get(i));
            }
            mapList.add(map);
            if(limit!=-1 && mapList.size()==limit){
                break;
            }
        }
        //循环数据拼接表头和表体数据
        List<SubClassVO> subClassVOList = new ArrayList<>();
        List<HeardVO> heardVOList = new ArrayList<>();
        List<Map<String, Object>> maps = buildHeardBody(mapList, heardVOList, isGetMaxHeardValue);
        subClassVOList.add(new SubClassVO(sheetName,heardVOList,maps));
        return subClassVOList;
    }

    /**
     * multipartFile转File
     **/
    public File multipartFile2File(MultipartFile multipartFile){
        File file = null;
        if (multipartFile != null){
            try {
                file=File.createTempFile("tmp", null);
                multipartFile.transferTo(file);
                System.gc();
                file.deleteOnExit();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return file;
    }
}
